package HHD.Sgg.UserADS

import org.apache.spark.sql.SparkSession

/**
 * 龚英杰：
 * 1.页面统计
 * 需求：
 *
字段                类型              备注
`dt`               STRING           '统计日期'(分区列),
`recent_days`      BIGINT           '日期范围',
`uv_count`         BIGINT           '访客人数',
`avg_duration_sec` BIGINT           '平均停留时长，单位为秒',
`avg_page_count`   BIGINT           '平均浏览页面数',
`sv_count`         BIGINT           '会话总数',
`bounce_rate`      DECIMAL(16, 2)   '跳出率'
 *(只考虑最近1天，即范围为今天和昨天，7天和30天暂不考虑)
 * 结果存入HHDtest.ads_traffic_stats_by_channel表
 */


object CountNum {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().getOrCreate()

    spark.sql(
      """
        |insert into table HHDtest.ads_traffic_stats_by_channel
        |from(from HHDtest.fact_page_residence_time
        |         select
        |             user_id,
        |             go_in_time,
        |             out_time,
        |             if(count(user_id) over(partition by user_id) = 1,1,0) s)t1
        |select
        |       '2024-04-25' dt,
        |       '2024-04-25_2026-04-26' recent_days,
        |       count(distinct user_id) uv_count,
        |       avg(cast(out_time as bigint) - cast(go_in_time as bigint)) avg_duration_sec,
        |       count(1)/count(distinct user_id) avg_page_count,
        |       count(1) sv_count,
        |       concat((sum(s)/count(1))*100,'%') bounce_rate
        |   where go_in_time < '2024-04-26 00:00:00' and go_in_time >= '2024-04-24 00:00:00'
        |""".stripMargin)

  }
}
